package com.picvik.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import com.picvik.util.MyLog;

public class TagDiscussionUtil {
	// DB related private variables
	private Connection connection = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;
	
	// Public util methods on picvik_TagDiscussion table.
	
	// Insertion utitlity methods.
	public void addNewTagDiscussion(TagDiscussionModel tagDiscussion){
		
		if(tagDiscussion == null){
			
			return;
		}
		
		// Add new tag-discussion
		
		try {
				        
    		//query string 
    		String qstring = "INSERT INTO picvik_tag_discussion (discussion_id, tag_id) " +
    				"VALUES("+ tagDiscussion.getDiscussion_id() +  "," + tagDiscussion.getTag_id()+  " );";
    		
    		MyLog.log(qstring);
    		
    		connection = com.picvik.util.MySqlConnection.getInstance().getConnection();
            
    		preparedStatement = connection.prepareStatement(qstring);
            
            preparedStatement.executeUpdate(qstring);
            
            preparedStatement.close();
            connection.close();
		
    	} catch (Exception e) {
    		e.printStackTrace();
    	}		
	}
	
	// Retrieval utility methods.
	
	// Get all discussion ids which has tags with given tag id.
	public ArrayList<Integer> getAllDiscussionIdsWithTagId(int tagid){
		ArrayList<Integer> resultDiscussionIds = new ArrayList<Integer>();
		
		try{
			
			// Most recent comment at end. so ASC
			String getdiscussionCommentQuery = "SELECT * FROM picvik_tag_discussion WHERE " +
					"tag_id = "+ tagid +" ORDER BY lastmodified ASC;";
			
			MyLog.log(getdiscussionCommentQuery);
			
			connection = com.picvik.util.MySqlConnection.getInstance().getConnection();
			
			preparedStatement = connection.prepareStatement(getdiscussionCommentQuery);
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next()){
				resultDiscussionIds.add(resultSet.getInt("discussion_id"));
			}	
			
			connection.close();
			preparedStatement.close();
			resultSet.close();
				
			}catch(Exception ex){
				connection = null;
				preparedStatement = null;
				resultSet = null;
				
				ex.printStackTrace();
			}

		return resultDiscussionIds;
	}
	
	// Get all discussion ids which has tags with given tag id.
		public ArrayList<Integer> getAllTagIdsWithDiscussionId(int descid){
			ArrayList<Integer> resultTagIds = new ArrayList<Integer>();
			
			try{
				
				// Most recent comment at end. so ASC
				String getdiscussionCommentQuery = "SELECT * FROM picvik_tag_discussion WHERE " +
						"discussion_id = "+ descid +" ;";
				
				MyLog.log(getdiscussionCommentQuery);
				
				connection = com.picvik.util.MySqlConnection.getInstance().getConnection();
				
				preparedStatement = connection.prepareStatement(getdiscussionCommentQuery);
				resultSet = preparedStatement.executeQuery();
				
				while(resultSet.next()){
					resultTagIds.add(resultSet.getInt("tag_id"));
				}	
				
				connection.close();
				preparedStatement.close();
				resultSet.close();
					
				}catch(Exception ex){
					connection = null;
					preparedStatement = null;
					resultSet = null;
					
					ex.printStackTrace();
				}

			return resultTagIds;
		}
}
